﻿using System;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Data;
using System.Linq;
using DBUtil.Expressions;
using System.Threading;
using DBUtil.Util;
using System.ComponentModel;
using System.Threading.Tasks;
using DBUtil.UsingBlocks;
using DotNetCommon;
using DotNetCommon.Data;
using DotNetCommon.Extensions;
using System.Data.Common;

namespace DBUtil
{
    /// <summary>
    /// 通用数据库访问对象
    /// </summary>
    public abstract partial class DBAccess
    {
        #region 长连接状态管理 OpenLongConnectOnce/CloseLongConnectOnce

        /// <summary>
        /// 异步尝试打开一个长连接
        /// </summary>
        public async Task<IDisposable> OpenLongConnectOnceAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            _keepConnectCounter++;
            if (IsClose) await Conn.OpenAsync(cancellationToken).ConfigureAwait(false);
            return new LongConnectBlockAsync(this);
        }

#if NET5_0_OR_GREATER
        /// <summary>
        /// 尝试停止一个长连接
        /// </summary>
        public async Task CloseLongConnectOnceAsync()
        {
            _keepConnectCounter--;
            if (_keepConnectCounter < 0) _keepConnectCounter = 0;
            if (!IsTran && !IsKeepConnect)
            {
                await Conn.CloseAsync().ConfigureAwait(false);
            }
        }
#endif

        #endregion

        #region 测试数据库连接
        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <returns></returns>
        public async Task<Result> OpenTestAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            try
            {
                using (await OpenLongConnectOnceAsync(cancellationToken)) { }
                return Result.Ok();
            }
            catch (Exception ex)
            {
                return Result.NotOk(ex?.Message);
            }
        }
        #endregion

        #region 执行sql语句 ExecuteSql...
        /// <summary>
        /// 执行单个sql语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="cancellationToken">sql语句</param>
        /// <returns>返回影响的行数</returns>
        public async virtual Task<int> ExecuteSqlAsync(string strSql, CancellationToken cancellationToken = default(CancellationToken)) => await ExecuteSqlBatchAsync(new string[] { strSql }, cancellationToken);

        /// <summary>
        /// 执行多个sql语句
        /// </summary>
        /// <param name="sqlArray">多个SQL语句的数组</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回总共影响的行数</returns>
        public async virtual Task<int> ExecuteSqlBatchAsync(string[] sqlArray, CancellationToken cancellationToken = default(CancellationToken))
        {
            var list = new List<ExecuteSqlBatchItem>();
            foreach (var sql in sqlArray)
            {
                list.Add(ExecuteSqlBatchItem.Create(sql));
            }
            return await ExecuteSqlBatchAsync(list.ToArray(), cancellationToken);
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">要执行的sql语句</param>
        /// <param name="paras">参数数组</param>
        /// <param name="cancellationToken"></param>
        /// <returns>受影响的行数</returns>
        public async virtual Task<int> ExecuteSqlAsync(string strSql, IDataParameter[] paras, CancellationToken cancellationToken = default(CancellationToken)) => await ExecuteSqlBatchAsync(new ExecuteSqlBatchItem[] { ExecuteSqlBatchItem.Create(strSql, paras) }, cancellationToken);

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">要执行的sql语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <param name="cancellationToken">用以生成参数数组的字典</param>
        /// <returns>受影响的行数</returns>
        public async virtual Task<int> ExecuteSqlAsync(string strSql, Dictionary<string, object> dic, CancellationToken cancellationToken = default(CancellationToken)) => await ExecuteSqlAsync(strSql, dic.ToParameters(this), cancellationToken);

        /// <summary>
        /// 批量执行带参数的sql语句
        /// </summary>
        /// <param name="items">批量执行项，参考: <seealso cref="ExecuteSqlBatchItem.Create(string, IDataParameter[])"/> </param>
        /// <param name="cancellationToken"></param>
        /// <returns>总共影响的行数</returns>
        public async virtual Task<int> ExecuteSqlBatchAsync(ExecuteSqlBatchItem[] items, CancellationToken cancellationToken = default(CancellationToken))
        {
            int counter = 0;
            var cmd = CreateCommand() as DbCommand;
            cmd.Connection = Conn;
            if (IsTran) cmd.Transaction = Tran;
            using (await OpenLongConnectOnceAsync(cancellationToken))
            {
                foreach (var item in items)
                {
                    cmd.CommandText = item.Sql;
                    cmd.Parameters.Clear();
                    if (item.UseParameterArray && item.Parameters?.Length > 0)
                    {
                        foreach (var para in item.Parameters) cmd.Parameters.Add(para);
                    }
                    else if (!item.UseParameterArray && item.Dic?.Count > 0)
                    {
                        foreach (var para in item.Dic.ToParameters(this)) cmd.Parameters.Add(para);
                    }
                    counter += await cmd.ExecuteNonQueryAsync(cancellationToken);
                }
                return counter;
            }
        }
        #endregion

        #region Insert...

        /// <summary>
        /// 向表中插入一行数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">列名和值的键值对;如果某项是原生sql,请参照: <code>dic.Add(("GETDATE()",false))</code></param>
        /// <param name="cancellationToken"></param>
        /// <returns>受影响的行数</returns>
        public virtual async Task<int> InsertAsync(string tableName, Dictionary<string, object> dic, CancellationToken cancellationToken = default(CancellationToken)) => await InsertBatchAsync(tableName, new Dictionary<string, object>[] { dic }, cancellationToken);

        /// <summary>
        /// 批量插入(相同表)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dics">多行数据</param>
        /// <param name="cancellationToken"></param>
        /// <returns>影响的行数</returns>
        public async virtual Task<int> InsertBatchAsync(string tableName, Dictionary<string, object>[] dics, CancellationToken cancellationToken = default(CancellationToken)) => await InsertBatchAsync(dics.Select(dic => InsertBatchItem.Create(tableName, dic)).ToArray(), cancellationToken);

        /// <summary>
        /// 批量插入数据(可批量向不同表插入)
        /// </summary>
        /// <param name="items">插入项，参照：<seealso cref="InsertBatchItem.Create(string, Dictionary{string, object})"/></param>
        /// <param name="cancellationToken"></param>
        /// <returns>影响的行数</returns>
        public async virtual Task<int> InsertBatchAsync(InsertBatchItem[] items, CancellationToken cancellationToken = default(CancellationToken))
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < items.Length; i++)
            {
                var item = items[i];
                string prefix = $"p{i}_";
                var res = SqlUtil.GenerateInsertSql(this, item.TableName, item.Dic, prefix);
                sql += res.sql + "\r\n";
                paras.AddRange(res.paras);
            }
            return await ExecuteSqlAsync(sql, paras.ToArray(), cancellationToken);
        }

        /// <summary>
        /// 插入一行数据并且返回自增的Id
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dic">列名和值的键值对;如果某项是原生sql,请参照: <code>dic.Add(("GETDATE()",false))</code></param>
        /// <param name="sequenceName">序列名称</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<long> InsertAndGetIdAsync(string tableName, Dictionary<string, object> dic, string sequenceName, CancellationToken cancellationToken = default(CancellationToken)) => (await InsertBatchAndGetIdsAsync(tableName, sequenceName, new Dictionary<string, object>[] { dic }, cancellationToken)).FirstOrDefault();

        /// <summary>
        /// 插入多行数据(相同表)并且返回自增的Id集合
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="sequenceName">序列名称</param>
        /// <param name="dics">多行数据</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<List<long>> InsertBatchAndGetIdsAsync(string tableName, string sequenceName, Dictionary<string, object>[] dics, CancellationToken cancellationToken = default(CancellationToken))
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < dics.Length; i++)
            {
                var item = dics[i];
                string prefix = $"p{i}_";
                var res = SqlUtil.GenerateInsertSql(this, tableName, item, prefix);
                sql += res.sql + "\r\n";
                sql += GetInsertedId(sequenceName);
                paras.AddRange(res.paras);
            }
            var dt = await SelectDataTableAsync(sql, paras.ToArray(), cancellationToken);
            return dt.Select().Select(i => i[0].To<long>()).ToList();
        }
        #endregion

        #region Update...

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="setDic">以键值对表示的更新列</param>
        /// <param name="whereSql">过滤条件sql以and开头</param>
        /// <param name="parameters">where子句中的参数</param>
        /// <param name="cancellationToken"></param>
        /// <returns>受影响的行数</returns>
        public async virtual Task<int> UpdateAsync(string tableName, Dictionary<string, object> setDic, string whereSql, IDataParameter[] parameters, CancellationToken cancellationToken = default(CancellationToken)) =>
            await UpdateBatchAsync(new UpdateBatchItem[] { UpdateBatchItem.Create(tableName, setDic, whereSql, parameters) }, cancellationToken);

        /// <summary>
        /// 更新数据，使用字典作为过滤条件
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="setDic">以键值对表示的更新列</param>
        /// <param name="whereDic">以键值对表示的过滤条件</param>
        /// <param name="cancellationToken"></param>
        /// <returns>受影响的行数</returns>
        public async virtual Task<int> UpdateAsync(string tableName, Dictionary<string, object> setDic, Dictionary<string, object> whereDic, CancellationToken cancellationToken = default(CancellationToken)) =>
            await UpdateBatchAsync(new UpdateBatchItem[] { UpdateBatchItem.Create(tableName, setDic, whereDic) }, cancellationToken);

        /// <summary>
        /// 批量更新,不同表
        /// </summary>
        /// <param name="items">更新项数组</param>
        /// <param name="cancellationToken"></param>
        /// <returns>受影响的行数</returns>
        public async virtual Task<int> UpdateBatchAsync(UpdateBatchItem[] items, CancellationToken cancellationToken = default(CancellationToken))
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < items.Length; i++)
            {
                var item = items[i];
                var (filterSql, paramter) = (item.WhereSql, item.Parameters);
                var hasPrefix = false;
                if (!item.UseRawSql)
                {
                    (filterSql, paramter) = SqlUtil.GenerateFilterSql(this, item.WhereDic, $"p_{i}", null);
                    hasPrefix = true;
                }
                var (sql2, paras2) = SqlUtil.GenerateUpdateSql(this, item.TableName, item.SetDic, hasPrefix ? "" : $"__p_{i}", filterSql, paramter);
                sql += "\r\n" + sql2;
                if (paras2 != null) paras.AddRange(paras2);
            }
            return await ExecuteSqlAsync(sql, paras.ToArray(), cancellationToken);
        }
        #endregion

        #region UpdateOrInsert

        /// <summary>
        /// 插入或更新
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertDic">以键值对表示的插入项</param>
        /// <param name="updateDic">以键值对表示的更新项</param>
        /// <param name="whereSql">用以决定是更新还是插入的where子句</param>
        /// <param name="parameters">where子句中的参数</param>
        /// <param name="cancellationToken"></param>
        /// <returns>受影响的行数</returns>
        public async virtual Task<int> UpdateOrInsertAsync(string tableName, Dictionary<string, object> insertDic, Dictionary<string, object> updateDic, string whereSql, IDataParameter[] parameters, CancellationToken cancellationToken = default(CancellationToken))
        {
            using (await OpenLongConnectOnceAsync(cancellationToken))
            {
                if ((await SelectScalarAsync<string>($"select count(1) from {tableName} where 1=1 {whereSql}", parameters, cancellationToken)) == "0")
                {
                    return await InsertAsync(tableName, insertDic, cancellationToken);
                }
                else
                {
                    return await UpdateAsync(tableName, updateDic, whereSql, parameters, cancellationToken);
                }
            };
        }

        /// <summary>
        /// 插入或更新
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertDic">以键值对表示的插入项</param>
        /// <param name="updateDic">以键值对表示的更新项</param>
        /// <param name="filterDic">用以生成where子句的字典，进一步决定是更新还是插入</param>
        /// <param name="cancellationToken"></param>
        /// <returns>受影响的行数</returns>
        public async virtual Task<int> UpdateOrInsertAsync(string tableName, Dictionary<string, object> insertDic, Dictionary<string, object> updateDic, Dictionary<string, object> filterDic, CancellationToken cancellationToken = default(CancellationToken))
        {
            var (filterSql, paras2) = SqlUtil.GenerateFilterSql(this, filterDic, $"p_", null);
            return await UpdateOrInsertAsync(tableName, insertDic, updateDic, filterSql, paras2, cancellationToken);
        }
        #endregion

        #region Delete

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereSql">过滤条件以and开头</param>
        /// <param name="parameters">where子句中的参数</param>
        /// <param name="cancellationToken"></param>
        /// <returns>受影响的行数</returns>
        public async virtual Task<int> DeleteAsync(string tableName, string whereSql, IDataParameter[] parameters, CancellationToken cancellationToken = default(CancellationToken))
            => await DeleteBatchAsync(new DeleteBatchItem[] { DeleteBatchItem.Create(tableName, whereSql, parameters) }, cancellationToken);

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereDic">用于生成where子句的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns>受影响的行数</returns>
        public async virtual Task<int> DeleteAsync(string tableName, Dictionary<string, object> whereDic, CancellationToken cancellationToken = default(CancellationToken))
            => await DeleteBatchAsync(tableName, new Dictionary<string, object>[] { whereDic }, cancellationToken);

        /// <summary>
        /// 批量删除(相同表)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="whereDics">用于生成where子句的字典数组</param>
        /// <param name="cancellationToken"></param>
        /// <returns>受影响的行数</returns>
        public async virtual Task<int> DeleteBatchAsync(string tableName, Dictionary<string, object>[] whereDics, CancellationToken cancellationToken = default(CancellationToken))
            => await DeleteBatchAsync(whereDics.Select(i => DeleteBatchItem.Create(tableName, i)).ToArray(), cancellationToken);

        /// <summary>
        /// 批量删除(不同表)
        /// </summary>
        /// <param name="items">删除项</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<int> DeleteBatchAsync(DeleteBatchItem[] items, CancellationToken cancellationToken = default(CancellationToken))
        {
            var sql = "";
            var paras = new List<IDataParameter>();
            for (int i = 0; i < items.Length; i++)
            {
                var item = items[i];
                var (filterSql, paramter) = (item.WhereSql, item.Parameters);
                if (!item.UseRawSql)
                {
                    (filterSql, paramter) = SqlUtil.GenerateFilterSql(this, item.WhereDic, $"p_{i}", null);
                }
                var resSql = SqlUtil.GenerateDeleteSql(this, item.TableName, filterSql);
                sql += resSql + "\r\n";
                if (paramter != null) paras.AddRange(paramter);
            }
            return await ExecuteSqlAsync(sql, paras.ToArray(), cancellationToken);
        }
        #endregion

        #region 查询语句 Select...

        /// <summary>
        /// 返回查到的第一行第一列的值
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="cancellationToken">cancellationToken</param>
        /// <returns>返回查到的第一行第一列的值</returns>
        public async virtual Task<T> SelectScalarAsync<T>(string strSql, CancellationToken cancellationToken = default(CancellationToken)) => await SelectScalarAsync<T>(strSql, new IDataParameter[] { }, cancellationToken);

        /// <summary>
        /// 返回查到的第一行第一列的值
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="dic">用以生成参数的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回查到的第一行第一列的值</returns>
        public async virtual Task<T> SelectScalarAsync<T>(string strSql, Dictionary<string, object> dic, CancellationToken cancellationToken = default(CancellationToken)) => await SelectScalarAsync<T>(strSql, dic.ToParameters(this), cancellationToken);

        /// <summary>
        /// 返回查到的第一行第一列的值
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回查到的第一行第一列的值</returns>
        public async virtual Task<T> SelectScalarAsync<T>(string strSql, IDataParameter[] paraArr, CancellationToken cancellationToken = default(CancellationToken))
        {
            object res = null;
            var cmd = CreateCommand();
            cmd.CommandText = strSql;
            cmd.Connection = Conn;
            if (IsTran) cmd.Transaction = Tran;
            if (paraArr != null)
            {
                foreach (var para in paraArr) cmd.Parameters.Add(para);
            }
            using (await OpenLongConnectOnceAsync(cancellationToken)) { res = await cmd.ExecuteScalarAsync(cancellationToken); }
            cmd.Parameters.Clear();
            return res.To<T>();
        }

        /// <summary>
        /// 返回查询结果的数据集
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回的查询结果集</returns>
        public async virtual Task<DataSet> SelectDataSetAsync(string strSql, CancellationToken cancellationToken = default(CancellationToken)) => await SelectDataSetAsync(strSql, new IDataParameter[] { }, cancellationToken);

        /// <summary>
        /// 返回查询结果的数据集,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回的查询结果集</returns>
        public async virtual Task<DataSet> SelectDataSetAsync(string strSql, Dictionary<string, object> dic, CancellationToken cancellationToken = default(CancellationToken)) => await SelectDataSetAsync(strSql, dic.ToParameters(this), cancellationToken);

        private static System.Reflection.MethodInfo _fillTable = null;

        /// <summary>
        /// 返回查询结果的数据集
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回的查询结果集</returns>
        public async virtual Task<DataSet> SelectDataSetAsync(string strSql, IDataParameter[] paraArr, CancellationToken cancellationToken = default(CancellationToken))
        {
            #region 反射方法

            if (_fillTable == null)
            {
                lock (typeof(DBAccess))
                {
                    if (_fillTable == null)
                    {
                        var type = typeof(DataAdapter);
                        var methods = type.GetMethods(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(i => i.Name == "Fill").ToList();
                        foreach (var item in methods)
                        {
                            var paras = item.GetParameters();
                            if (paras[0]?.ParameterType == typeof(DataTable) && paras[1]?.ParameterType == typeof(IDataReader))
                            {
                                _fillTable = item;
                                break;
                            }
                        }
                    }
                }
            }
            #endregion
            var dataTables = new List<DataTable>();
            using (await OpenLongConnectOnceAsync())
            {
                using var reader = await SelectDataReaderAsync(strSql, cancellationToken);
                using var adapter = CreateAdapter();
                do
                {
                    var dataTable = new DataTable();
                    _fillTable.Invoke(adapter, new object[] { dataTable, reader.Reader });
                    dataTables.Add(dataTable);
                } while (reader.Reader.NextResult());
            }
            var ds = new DataSet();
            ds.Tables.AddRange(dataTables.ToArray());
            return ds;
        }

        /// <summary>
        /// 返回查询结果的数据表
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回的查询结果集</returns>
        public async virtual Task<DataTable> SelectDataTableAsync(string strSql, CancellationToken cancellationToken = default(CancellationToken)) => await SelectDataTableAsync(strSql, new IDataParameter[] { }, cancellationToken);

        /// <summary>
        /// 返回查询结果的数据表,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回的查询结果集</returns>
        public async virtual Task<DataTable> SelectDataTableAsync(string strSql, Dictionary<string, object> dic, CancellationToken cancellationToken = default(CancellationToken)) => await SelectDataTableAsync(strSql, dic.ToParameters(this), cancellationToken);

        /// <summary>
        /// 返回查询结果的数据表
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回的查询结果集</returns>
        public async virtual Task<DataTable> SelectDataTableAsync(string strSql, IDataParameter[] paraArr, CancellationToken cancellationToken = default(CancellationToken))
        {
            var ds = await SelectDataSetAsync(strSql, paraArr, cancellationToken);
            if (ds.Tables.Count > 0) return ds.Tables[0];
            else return null;
        }

        /// <summary>
        /// 获取阅读器
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回阅读器</returns>
        public async virtual Task<ReaderLongConnectBlock> SelectDataReaderAsync(string strSql, CancellationToken cancellationToken = default(CancellationToken)) => await SelectDataReaderAsync(strSql, new IDataParameter[] { }, cancellationToken);

        /// <summary>
        /// 获取阅读器,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回阅读器</returns>
        public async virtual Task<ReaderLongConnectBlock> SelectDataReaderAsync(string strSql, Dictionary<string, object> dic, CancellationToken cancellationToken = default(CancellationToken)) => await SelectDataReaderAsync(strSql, dic.ToParameters(this), cancellationToken);

        /// <summary>
        /// 获取阅读器
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="paraArr">sql语句参数</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回阅读器</returns>
        public async virtual Task<ReaderLongConnectBlock> SelectDataReaderAsync(string strSql, IDataParameter[] paraArr, CancellationToken cancellationToken = default(CancellationToken))
        {
            var cmd = CreateCommand();
            cmd.CommandText = strSql;
            cmd.Connection = Conn;
            if (paraArr != null)
            {
                foreach (var para in paraArr) cmd.Parameters.Add(para);
            }
            if (IsTran) cmd.Transaction = Tran;
            var block = _openReaderLongConnectOnce();
            IDataReader reader = await cmd.ExecuteReaderAsync(cancellationToken);
            cmd.Parameters.Clear();
            block.Reader = reader;
            return block;
        }

        /// <summary>
        /// 查询到单个模型
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<T> SelectModelAsync<T>(string sql, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new() => (await SelectModelsAsync<T>(sql, new IDataParameter[] { }, cancellationToken)).FirstOrDefault();

        /// <summary>
        /// 查询到单个模型,使用匿名对象作为sql语句参数
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<T> SelectModelAsync<T>(string sql, Dictionary<string, object> dic, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new() => await SelectModelAsync<T>(sql, dic.ToParameters(this), cancellationToken);

        /// <summary>
        /// 查询到单个模型,使用表名快速查询
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="whereDic">生成where子句的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<T> SelectModelFastAsync<T>(string tableName, Dictionary<string, object> whereDic, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new() => (await SelectModelsFastAsync<T>(tableName, whereDic, cancellationToken)).FirstOrDefault();

        /// <summary>
        /// 查询到模型集合,使用表名快速查询
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="whereDic">生成where子句的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<List<T>> SelectModelsFastAsync<T>(string tableName, Dictionary<string, object> whereDic, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new()
        {
            var (filter, paras2) = SqlUtil.GenerateFilterSql(this, whereDic, "p_", null);
            var sql = $"select * from {tableName} where 1=1 {filter}";
            return await SelectModelsAsync<T>(sql, paras2, cancellationToken);
        }

        /// <summary>
        /// 查询到单个模型
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<T> SelectModelAsync<T>(string sql, IDataParameter[] filterParameters, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new() => (await SelectModelsAsync<T>(sql, filterParameters, cancellationToken)).FirstOrDefault();

        /// <summary>
        /// 查询到模型集合
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<List<T>> SelectModelsAsync<T>(string sql, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new() => await SelectModelsAsync<T>(sql, new IDataParameter[] { }, cancellationToken);

        /// <summary>
        /// 查询到模型集合,使用匿名对象作为sql语句参数
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<List<T>> SelectModelsAsync<T>(string sql, Dictionary<string, object> dic, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new() => await SelectModelsAsync<T>(sql, dic.ToParameters(this), cancellationToken);

        /// <summary>
        /// 查询到模型集合
        /// </summary>
        /// <typeparam name="T">要映射到的模型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<List<T>> SelectModelsAsync<T>(string sql, IDataParameter[] filterParameters, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new() => (await SelectDataTableAsync(sql, filterParameters, cancellationToken)).ToModels<T>();

        /// <summary>
        /// 查询到字典
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<Dictionary<string, object>> SelectDictionaryAsync(string sql, CancellationToken cancellationToken = default(CancellationToken)) => (await SelectDictionarysAsync(sql, new IDataParameter[] { }, cancellationToken)).FirstOrDefault();

        /// <summary>
        /// 查询到字典集合,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<Dictionary<string, object>> SelectDictionaryAsync(string sql, Dictionary<string, object> dic, CancellationToken cancellationToken = default(CancellationToken)) => (await SelectDictionarysAsync(sql, dic.ToParameters(this), cancellationToken)).FirstOrDefault();

        /// <summary>
        /// 查询到字典
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<Dictionary<string, object>> SelectDictionaryAsync(string sql, IDataParameter[] filterParameters, CancellationToken cancellationToken = default(CancellationToken)) => (await SelectDictionarysAsync(sql, filterParameters, cancellationToken)).FirstOrDefault();

        /// <summary>
        /// 查询到字典集合
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<List<Dictionary<string, object>>> SelectDictionarysAsync(string sql, CancellationToken cancellationToken = default(CancellationToken)) => await SelectDictionarysAsync(sql, new IDataParameter[] { }, cancellationToken);

        /// <summary>
        /// 查询到字典集合,使用匿名对象作为sql语句参数
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="dic">用以生成参数数组的字典</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<List<Dictionary<string, object>>> SelectDictionarysAsync(string sql, Dictionary<string, object> dic, CancellationToken cancellationToken = default(CancellationToken)) => await SelectDictionarysAsync(sql, dic.ToParameters(this), cancellationToken);

        /// <summary>
        /// 查询到字典集合
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="filterParameters">查询语句中的参数数组</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async virtual Task<List<Dictionary<string, object>>> SelectDictionarysAsync(string sql, IDataParameter[] filterParameters, CancellationToken cancellationToken = default(CancellationToken)) => (await SelectDataTableAsync(sql, filterParameters, cancellationToken)).ToDictionaryList();

        #endregion

        #region 事务操作
#if NET5_0_OR_GREATER
        /// <summary>
        /// 开启一次事务(不支持嵌套事务,如果当前已经在事务中并且隔离级别大于新指定的隔离级别,那么就忽略本次的开启事务操作)
        /// </summary>
        /// <param name="isolationLevel">事务隔离级别(默认为:ReadCommitted)</param>
        /// <param name="cancellationToken"></param>
        private async Task BeginTransactionOnceAsync(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted, CancellationToken cancellationToken = default(CancellationToken))
        {
            if (IsTran && Tran.IsolationLevel < isolationLevel) throw new Exception($"已经在事务中,并且当前的事务隔离级别({Tran.IsolationLevel.ToString()})小于新指定的隔离级别({isolationLevel})!");
            KeepTransactionCount++;
            if (!IsTran)
            {
                await OpenLongConnectOnceAsync(cancellationToken);
                Tran = await Conn.BeginTransactionAsync(isolationLevel, cancellationToken);
            }
        }

        /// <summary>
        /// 提交一次事务
        /// </summary>
        private async Task CommitTransactionOnceAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            cancellationToken.ThrowIfCancellationRequested();
            KeepTransactionCount--;
            if (KeepTransactionCount < 0) KeepTransactionCount = 0;
            if (IsTran && KeepTransactionCount == 0)
            {
                Tran.Commit();
                Tran = null;
                await CloseLongConnectOnceAsync();
            }
        }

        /// <summary>
        /// 回滚一次事务
        /// </summary>
        private async Task RollbackTransactionOnceAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            KeepTransactionCount--;
            if (KeepTransactionCount < 0) KeepTransactionCount = 0;
            if (IsTran && KeepTransactionCount == 0)
            {
                await Tran.RollbackAsync(cancellationToken);
                Tran = null;
                await CloseLongConnectOnceAsync();
            }
        }

        /// <summary>
        /// 在事务内运行,保证异常时事务回滚(try{}catch{roollback})
        /// </summary>
        /// <param name="isolationLevel">事务隔离级别</param>
        /// <param name="action">要运行的代码</param>
        /// <param name="cancellationToken"></param>
        public async Task RunInTransactionAsync(Func<CancellationToken, Task> action, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted, CancellationToken cancellationToken = default(CancellationToken))
        {
            try
            {
                await BeginTransactionOnceAsync(isolationLevel, cancellationToken);
                await action(cancellationToken);
                await CommitTransactionOnceAsync(cancellationToken);
            }
            catch
            {
                await RollbackTransactionOnceAsync(cancellationToken);
                throw;
            }
        }

        /// <summary>
        /// 在事务内运行,保证异常时事务回滚(try{}catch{roollback})
        /// </summary>
        /// <param name="isolationLevel">事务隔离级别</param>
        /// <param name="func">要运行的代码</param>
        /// <param name="cancellationToken"></param>
        public async Task<T> RunInTransactionAsync<T>(Func<CancellationToken, Task<T>> func, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted, CancellationToken cancellationToken = default(CancellationToken))
        {
            try
            {
                await BeginTransactionOnceAsync(isolationLevel, cancellationToken);
                var res = await func(cancellationToken);
                await CommitTransactionOnceAsync(cancellationToken);
                return res;
            }
            catch
            {
                await RollbackTransactionOnceAsync(cancellationToken);
                throw;
            }
        }
#endif
        #endregion

        #region Judge系列: 判断表/视图/列/存储过程/触发器是否存在

        /// <summary>
        /// 判断指定表或视图中是否有某一列
        /// </summary>
        /// <param name="tableOrViewName">表或视图名</param>
        /// <param name="columnName">列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回列是否存在</returns>
        public async virtual Task<bool> JudgeColumnExistInTableOrViewAsync(string tableOrViewName, string columnName, CancellationToken cancellationToken = default(CancellationToken))
        {
            string sql = $"select count(1) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='{tableOrViewName}' and COLUMN_NAME='{columnName}'";
            var res = await SelectScalarAsync<string>(sql, cancellationToken);
            int r = res.ToWithDefault<int>(0);
            return r > 0;
        }

        /// <summary>
        /// 判断指定表中是否有某一列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回列是否存在</returns>
        public async virtual Task<bool> JudgeColumnExistInTableAsync(string tableName, string columnName, CancellationToken cancellationToken = default(CancellationToken))
        {
            string sql = $"select count(1) from INFORMATION_SCHEMA.COLUMNS c left join INFORMATION_SCHEMA.tables t on c.TABLE_CATALOG=t.TABLE_CATALOG and c.TABLE_SCHEMA=t.TABLE_SCHEMA and c.TABLE_NAME=t.TABLE_NAME  where c.COLUMN_NAME='{columnName}' and  c.TABLE_NAME='{columnName}' and t.TABLE_TYPE='BASE TABLE'";
            var res = await SelectScalarAsync<string>(sql, cancellationToken);
            int r = res.ToWithDefault<int>(0);
            return r > 0;
        }

        /// <summary>
        /// 判断指定视图中是否有某一列
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <param name="columnName">列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回列是否存在</returns>
        public async virtual Task<bool> JudgeColumnExistInViewAsync(string viewName, string columnName, CancellationToken cancellationToken = default(CancellationToken))
        {
            string sql = $"select count(1) from INFORMATION_SCHEMA.COLUMNS c left join INFORMATION_SCHEMA.tables t on c.TABLE_CATALOG=t.TABLE_CATALOG and c.TABLE_SCHEMA=t.TABLE_SCHEMA and c.TABLE_NAME=t.TABLE_NAME  where c.COLUMN_NAME='{viewName}' and  c.TABLE_NAME='{columnName}' and t.TABLE_TYPE='VIEW'";
            var res = await SelectScalarAsync<string>(sql, cancellationToken);
            int r = res.ToWithDefault<int>(0);
            return r > 0;
        }

        /// <summary>
        /// 判断表或视图是否存在
        /// </summary>
        /// <param name="tableOrViewName">表或视图名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回表或视图是否存在</returns>
        public async virtual Task<bool> JudgeTableOrViewExistAsync(string tableOrViewName, CancellationToken cancellationToken = default(CancellationToken))
        {
            string sql = string.Format("select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{0}'", tableOrViewName);
            int r = await SelectScalarAsync<int>(sql, cancellationToken);
            return r > 0;
        }

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回表是否存在</returns>
        public async virtual Task<bool> JudgeTableExistAsync(string tableName, CancellationToken cancellationToken = default(CancellationToken))
        {
            string sql = string.Format("select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{0}' and TABLE_TYPE='BASE TABLE'", tableName);
            int r = await SelectScalarAsync<int>(sql, cancellationToken);
            return r > 0;
        }

        /// <summary>
        /// 判断视图是否存在
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <param name="cancellationToken"></param>
        /// <returns>返回视图是否存在</returns>
        public async virtual Task<bool> JudgeViewExistAsync(string viewName, CancellationToken cancellationToken = default(CancellationToken))
        {
            string sql = string.Format("select count(1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME='{0}' and TABLE_TYPE='VIEW'", viewName);
            int r = await SelectScalarAsync<int>(sql, cancellationToken);
            return r > 0;
        }

        /// <summary>
        /// 判断存储过程是否存在
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public abstract Task<bool> JudgeProcedureExistAsync(string procName, CancellationToken cancellationToken = default(CancellationToken));

        /// <summary>
        /// 判断触发器是否存在
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public abstract Task<bool> JudgeTriggerExistAsync(string triggerName, CancellationToken cancellationToken = default(CancellationToken));
        #endregion

        #region Id和流水号生成控制器

        /// <summary>
        /// 根据指定的表名和列名生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected abstract Task<long> InternalNewIdAsync(string tableName, string colName, CancellationToken cancellationToken = default(CancellationToken));
        /// <summary>
        /// 根据指定的表名和列名生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public virtual Task<long> NewIdAsync(string tableName, string colName, CancellationToken cancellationToken = default(CancellationToken)) => InternalNewIdAsync(tableName, colName, cancellationToken);

        /// <summary>
        /// 根据指定的表名和列名批量生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <param name="cancellationToken"></param> 
        /// <returns></returns>
        protected abstract Task<List<long>> InternalNewIdsAsync(string tableName, string colName, int count, CancellationToken cancellationToken = default(CancellationToken));

        /// <summary>
        /// 根据指定的表名和列名批量生成Id(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">指定的表名</param>
        /// <param name="colName">指定的列名</param>
        /// <param name="count">生成的Id的数量</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public virtual Task<List<long>> NewIdsAsync(string tableName, string colName, int count, CancellationToken cancellationToken = default(CancellationToken))
            => InternalNewIdsAsync(tableName, colName, count, cancellationToken);

        /// <summary>
        /// 重置一个表的Id生成控制,这将删除数据库缓存的生成记录(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected virtual async Task InternalResetIdAsync(string tableName, string colName, CancellationToken cancellationToken = default(CancellationToken))
        {
            EnsureInitGenerator();
            await DeleteAsync(Settings.DBCacheGeneratorIdTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname", new IDataParameter[] { CreatePara("tablename", tableName), CreatePara("colname", colName) }, cancellationToken);
        }
        /// <summary>
        /// 重置一个表的Id生成控制,这将删除数据库缓存的生成记录(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public virtual Task ResetIdAsync(string tableName, string colName, CancellationToken cancellationToken = default(CancellationToken))
            => InternalResetIdAsync(tableName, colName, cancellationToken);

        /// <summary>
        /// 根据表名和列名生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected abstract Task<string> InternalNewSNOAsync(string tableName, string colName, SerialFormat format, CancellationToken cancellationToken = default(CancellationToken));
        /// <summary>
        /// 根据表名和列名生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public virtual Task<string> NewSNOAsync(string tableName, string colName, SerialFormat format, CancellationToken cancellationToken = default(CancellationToken)) => InternalNewSNOAsync(tableName, colName, format, cancellationToken);

        /// <summary>
        /// 根据表名和列名批量生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        protected abstract Task<List<string>> InternalNewSNOsAsync(string tableName, string colName, SerialFormat format, int count, CancellationToken cancellationToken = default(CancellationToken));

        /// <summary>
        /// 根据表名和列名批量生成流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="format">流水号格式</param>
        /// <param name="count">生成的流水号的数量</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public virtual Task<List<string>> NewSNOsAsync(string tableName, string colName, SerialFormat format, int count, CancellationToken cancellationToken = default(CancellationToken)) => InternalNewSNOsAsync(tableName, colName, format, count, cancellationToken);

        /// <summary>
        /// 重置流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="serialFormat">格式</param>
        /// <param name="cancellationToken"></param>
        protected virtual async Task InternalResetSNOAsync(string tableName, string colName, SerialFormat serialFormat, CancellationToken cancellationToken = default(CancellationToken))
        {
            EnsureInitGenerator();
            tableName = QuoteIdentifierWithDefaultSchema(tableName, DefaultSchema);
            colName = QuoteIdentifierIfNot(colName);
            if (serialFormat != null)
            {
                var text = PrePareForSNO(serialFormat, DateTime.Now, DotNetCommon.Machine.MachineIdString).statictext;
                await DeleteAsync(Settings.DBCacheGeneratorSNOTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname and statictext={ParaPrefix}statictext", new IDataParameter[] { CreatePara("tablename", tableName), CreatePara("colname", colName), CreatePara("statictext", text) }, cancellationToken);
            }
            else
            {
                await DeleteAsync(Settings.DBCacheGeneratorSNOTableName, $" and tablename={ParaPrefix}tablename and colname={ParaPrefix}colname", new IDataParameter[] { CreatePara("tablename", tableName), CreatePara("colname", colName) }, cancellationToken);
            }
        }
        /// <summary>
        /// 重置流水号(基于数据库缓存)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="serialFormat"></param>
        /// <param name="cancellationToken"></param>
        public virtual Task ResetSNOAsync(string tableName, string colName, SerialFormat serialFormat, CancellationToken cancellationToken = default(CancellationToken)) => InternalResetSNOAsync(tableName, colName, serialFormat, cancellationToken);
        #endregion
    }
}
